package com.bojx.report.dao;

import com.jfinal.kit.Kv;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;
import com.jfinal.plugin.activerecord.SqlPara;

public class QuarterReportDao {
	/**
	 * 查询所在行号
	 * @param bank
	 * @return
	 */
	public int queryRowNum(String bank){
		SqlPara sqlPara=Db.getSqlPara("quarter.queryRowNum", bank);
		return Db.findFirst(sqlPara).getInt("row_num");
	}
	
	/**
	 * 查询是否上传过
	 * 
	 * 
	 */
	public Record queryUploadExcel(String bank,int year,int quarter){
		Kv con=Kv.by("bank", bank).set("year", year).set("quarter", quarter);
		SqlPara sqlPara=Db.getSqlPara("quarter.queryUploadExcel", con);
		return Db.findFirst(sqlPara);
	}
	
	/***
	 * 按行别汇总业务数据
	 */
	
	public Record sumBusinessDataByBank(String bank,String billType,int scope,int year,int quarter){
		Kv con=Kv.by("bank", bank).set("bill_type", billType).set("scope", scope)
				.set("year", year).set("quarter", quarter);
		SqlPara sqlPara=Db.getSqlPara("quarter.sumBusinessDataByBank", con);
		return Db.findFirst(sqlPara);
	}
	
	/***
	 * 按行别汇总业务数据(累计)
	 */
	public Record sumAddBusinessDataByBank(String bank, String billType, int scope, int year, int quarter) {
		Kv con=Kv.by("bank", bank).set("bill_type", billType).set("scope", scope)
				.set("year", year).set("quarter", quarter);
		SqlPara sqlPara=Db.getSqlPara("quarter.sumAddBusinessDataByBank", con);
		return Db.findFirst(sqlPara);
	}
	/***
	 * 按地区汇总业务数据
	 */
	public Record sumBusinessDataByArea(String area,String billType,int scope,int year,int quarter){
		Kv con=Kv.by("area", area).set("bill_type", billType).set("scope", scope)
				.set("year", year).set("quarter", quarter);
		SqlPara sqlPara=Db.getSqlPara("quarter.sumBusinessDataByArea", con);
		return Db.findFirst(sqlPara);
	}
	
	/***
	 * 按地区汇总业务数据(累计)
	 */
	public Record sumAddBusinessDataByArea(String area, String billType, int scope, int year, int quarter) {
		Kv con=Kv.by("area", area).set("bill_type", billType).set("scope", scope)
				.set("year", year).set("quarter", quarter);
		SqlPara sqlPara=Db.getSqlPara("quarter.sumAddBusinessDataByArea", con);
		return Db.findFirst(sqlPara);
	}
	/***
	 * 按行别汇总客户数据
	 */
	public Record sumClientDataByBank(String bank,int year,int quarter){
		Kv con=Kv.by("bank", bank).set("year", year).set("quarter", quarter);
		SqlPara sqlPara=Db.getSqlPara("quarter.sumClientDataByBank", con);
		return Db.findFirst(sqlPara);
	}
	/***
	 * 按行别汇总客户数据(累计)
	 */
	public Record sumAddClientDataByBank(String bank, int year, int quarter) {
		Kv con=Kv.by("bank", bank).set("year", year).set("quarter", quarter);
		SqlPara sqlPara=Db.getSqlPara("quarter.sumAddClientDataByBank", con);
		return Db.findFirst(sqlPara);
	}
	/***
	 * 按地区汇总客户数据
	 */
	public Record sumClientDataByArea(String area,int year,int quarter){
		Kv con=Kv.by("area", area).set("year", year).set("quarter", quarter);
		SqlPara sqlPara=Db.getSqlPara("quarter.sumClientDataByArea", con);
		return Db.findFirst(sqlPara);
	}
	
	/***
	 * 按地区汇总客户数据(累计)
	 */
	public Record sumAddClientDataByArea(String area, int year, int quarter) {
		Kv con=Kv.by("area", area).set("year", year).set("quarter", quarter);
		SqlPara sqlPara=Db.getSqlPara("quarter.sumAddClientDataByArea", con);
		return Db.findFirst(sqlPara);
	}
	
	/***
	 * 按行别汇总网点数据
	 */
	public Record sumBranchDataByBank(String bank,int year,int quarter){
		Kv con=Kv.by("bank", bank).set("year", year).set("quarter", quarter);
		SqlPara sqlPara=Db.getSqlPara("quarter.sumBranchDataByBank", con);
		return Db.findFirst(sqlPara);
	}
	
	/***
	 * 按行别汇总网点数据(累计)
	 */
	public Record sumAddBranchDataByBank(String bank, int year, int quarter) {
		Kv con=Kv.by("bank", bank).set("year", year).set("quarter", quarter);
		SqlPara sqlPara=Db.getSqlPara("quarter.sumAddBranchDataByBank", con);
		return Db.findFirst(sqlPara);
	}
	/**
	 * 按地区汇总网点数据
	 */
	public Record sumBranchDataByArea(String area,int year,int quarter){
		Kv con=Kv.by("area", area).set("year", year).set("quarter", quarter);
		SqlPara sqlPara=Db.getSqlPara("quarter.sumBranchDataByArea", con);
		return Db.findFirst(sqlPara);
	}
	/**
	 * 按地区汇总网点数据(累计)
	 */
	public Record sumAddBranchDataByArea(String area, int year, int quarter) {
		Kv con=Kv.by("area", area).set("year", year).set("quarter", quarter);
		SqlPara sqlPara=Db.getSqlPara("quarter.sumAddBranchDataByArea", con);
		return Db.findFirst(sqlPara);
	}

	
}
